﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

namespace AirlineCommon
{
    public class BusinessClass:TravelClass
    {
        //private const string CONNECTION_STRING = "AirlineConnectionString";
        public override float GetFareAmount()
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select BusinessClassFare from  Sector where SectorID=@SectorID";
            float _BusinessClassFare = 0;
            try
            {
                SqlParameter pSecID = new SqlParameter("@SectorID", SectorID);
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pSecID);
                if (ds.Tables[0].Rows.Count > 0)
                    _BusinessClassFare = float.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _BusinessClassFare = 0;
                return _BusinessClassFare;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Fare Amount", ex, "BusinessClass", "GetFareAmount");
                throw;
            }
        }

        public override int GetSeatAvailable(string flightNo, DateTime flightdate)
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select BusinessClassSeatsAvailable from  ScheduledFlights where FlightNo=@FlightNo And";
            sql += " Day(FlightDate)=@FlightDay And Month(FlightDate)=@FlightMonth And Year(FlightDate)=@FlightYear";
            int _BusinessClassSeatsAvailable = 0;
            try
            {
                SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                SqlParameter pFlightDay = new SqlParameter("@FlightDay", flightdate.Day);
                SqlParameter pFlightMonth = new SqlParameter("@FlightMonth", flightdate.Month);
                SqlParameter pFlightYear = new SqlParameter("@FlightYear", flightdate.Year);
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pFlightNo,pFlightDay,pFlightMonth,pFlightYear);
                if (ds.Tables[0].Rows.Count > 0)
                    _BusinessClassSeatsAvailable = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _BusinessClassSeatsAvailable = 0;
                return _BusinessClassSeatsAvailable;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Business Seat Available", ex, "BusinessClass", "GetSeatAvailable");
                throw;
            }
        }

        public override void UpdateClassSeat(string flightNo, DateTime FlightDate)
        {
            SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
            string SQL = "Update ScheduledFlights Set BusinessClassSeatsAvailable=BusinessClassSeatsAvailable-1 ";
            SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

            SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
            SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
            SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
            SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

            int HowManyRows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL, pFlightNo,
                    pDayFlight,
                    pMonthFlight,
                    pYearFlight);
            if (HowManyRows != 1)
                throw new Exception("Update ScheduledFlights fail.");
            // sqlComm.Dispose();
            conn.Close();
            return;
        }

        public override int UpdateClassSeatForReservation(SqlTransaction trans, string flightNo, DateTime FlightDate)
        {
            try

            {
                string SQL = "Update ScheduledFlights Set BusinessClassSeatsAvailable=BusinessClassSeatsAvailable-1 ";
                SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

                SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
                SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
                SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

                int HowManyRows = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL, pFlightNo,
                        pDayFlight,
                        pMonthFlight,
                        pYearFlight);
                return HowManyRows;
            }
            catch
            {
                return -1;
            }
        }

        public override int UpdateClassSeatForCancellation(SqlTransaction trans, string flightNo, DateTime FlightDate)
        {
            try
            {
                string SQL = "Update ScheduledFlights Set BusinessClassSeatsAvailable=BusinessClassSeatsAvailable+1 ";
                SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

                SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
                SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
                SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

                int HowManyRows = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL, pFlightNo,
                        pDayFlight,
                        pMonthFlight,
                        pYearFlight);
                return HowManyRows;
            }
            catch
            {
                return -1;
            }
        }
    }
}
